﻿create database fat1_c1203i_s3_g2
go
use fat1_c1203i_s3_g2
go
create table customer
(
	customerId int primary key identity ,
	username varchar(25) not null,
	passwords varchar(25) not null,
	name varchar(25),
	birthday datetime,
	gender varchar(25),
	email varchar(200) not null,
	status varchar(25) not null
)
--select * from customer where status = 'subcrible'
insert into customer values('thanh','123456','van thanh','04-26-1989','Male','vanthanh26041989@gmail.com','normal')
insert into customer values('linh','123456','linh','04-26-1989','Female','linhnt_c00721@fpt.aptech.ac.vn','subcrible')
insert into customer values('quang','123456','quang','04-26-1989','Male','quangtm_a05097@fpt.aptech.ac.vn','subcrible')
insert into customer values('test1','123456','test1','04-26-1989','Male','vanthanh26041989@gmail.com','normal')
insert into customer values('test2','123456','test2','04-26-1989','Male','vanthanh26041989@gmail.com','normal')
insert into customer values('test3','123456','test3','04-26-1989','Male','vanthanh26041989@gmail.com','normal')
insert into customer values('test4','123456','test4','04-26-1989','Male','vanthanh26041989@gmail.com','normal')
go

create table contactEmail
(
	contactEmailId int primary key identity,
	customerId int foreign key references customer(customerId),
	email varchar(100) not null,
	birthday datetime not null,
	name varchar(25) not null
)


insert into contactEmail values(2,'nguoimua12@gmail.com',GETDATE(),'linh')
insert into contactEmail values(2,'nguoiban1234567@gmail.com',GETDATE(),'quang')
insert into contactEmail values(3,'nguoimua12@gmail.com',GETDATE(),'thang')
insert into contactEmail values(3,'nguoiban1234567@gmail.com',GETDATE(),'minh')
go
create table subcribleType
(
	subcribleTypeId int primary key identity,
	subcrileName varchar(25) not null,
	price int not null
)
--select * from subcribleType

insert into subcribleType values('1month',10)
insert into subcribleType values('3month',20)
insert into subcribleType values('6month',40)
insert into subcribleType values('12month',60)
go
create table subcrible
(
	subcribleId int primary key identity,
	customerId int foreign key references customer(customerId),
	datefrom datetime not null,
	dateto datetime not null,
	subcribleTypeId int foreign key references subcribleType(subcribleTypeId),
)

go
insert into subcrible values(2,GETDATE(),DateAdd(MM, 1, GetDate()),1)
insert into subcrible values(3,GETDATE(),DateAdd(MM, 3, GetDate()),2)
go
create table typeEcard
(
	typeEcardId int primary key identity,
	typeCardName varchar(25) not null
)
--select * from typeEcard

insert into typeEcard values('Birthday')
insert into typeEcard values('Wedding')
insert into typeEcard values('NewYear')
insert into typeEcard values('Halloween')
insert into typeEcard values('Festivals')
insert into typeEcard values('valentive')
insert into typeEcard values('HappyHolidays')
insert into typeEcard values('Marry chistmas')
insert into typeEcard values('HappyEveryday')
insert into typeEcard values('Friendship')
insert into typeEcard values('ThankYou')
insert into typeEcard values('Family')
insert into typeEcard values('NewCard')
go
create table Ecard
(
	ecardId int primary key identity,
	urlImage varchar(200) not null,
	urlFlash varchar(200) not null,
	ecardName varchar(50) not null,
	addDate datetime not null,
	typeEcardId int foreign key references typeEcard(typeEcardId),
	counts int not null
)


-- thiep chuc mung sinh  nhat

insert into Ecard values('http://i.123g.us/c/birth_happybirthday/th/119311_th.gif','http://i.123g.us/c/birth_happybirthday/card/119311.swf','Wish You Health.','07-11-2013',1,0)
insert into Ecard values('http://i.123g.us/c/birth_happybirthday/th/114550_th.gif','http://i.123g.us/c/birth_happybirthday/card/114550.swf','A Card Full Of Surprise.','07-11-2013',1,0)
insert into Ecard values('http://i.123g.us/c/birth_happybirthday/th/124599_th.gif','http://i.123g.us/c/birth_happybirthday/card/124599.swf','Beautiful Flowers.','07-11-2013',1,0)
insert into Ecard values('http://i.123g.us/c/birth_happybirthday/th/117069_th.gif','http://i.123g.us/c/birth_happybirthday/card/117069.swf','An Interactive Birthday.','07-11-2013',1,0)
insert into Ecard values('http://i.123g.us/c/birth_happybirthday/th/303331_th.jpg','http://i.123g.us/c/birth_happybirthday/card/303331.swf','Wishing You Blessings.','07-11-2013',1,0)
insert into Ecard values('http://i.123g.us/c/birth_happybirthday/th/102896_th.gif','http://i.123g.us/c/birth_happybirthday/card/102896.swf','A Special Birthday.','07-11-2013',1,0)
insert into Ecard values('http://i.123g.us/c/birth_happybirthday/th/120570_th.gif','http://i.123g.us/c/birth_happybirthday/card/120570.swf','Wish You Smiles.','07-11-2013',1,0)
insert into Ecard values('http://i.123g.us/c/birth_happybirthday/th/120819_th.gif','http://i.123g.us/c/birth_happybirthday/card/120819.swf','This Day Be A Magical.','07-11-2013',1,0)
insert into Ecard values('http://i.123g.us/c/birth_happybirthday/th/123206_th.gif','http://i.123g.us/c/birth_happybirthday/card/123206.swf','That Celebrates You.','07-11-2013',1,0)
insert into Ecard values('http://i.123g.us/c/birth_happybirthday/th/123057_th.gif','http://i.123g.us/c/birth_happybirthday/card/123057.swf','Time To Celebrat.','07-11-2013',1,0)
insert into Ecard values('http://i.123g.us/c/birth_happybirthday/th/123923_th.gif','http://i.123g.us/c/birth_happybirthday/card/123923.swf','Special Day To Celebrate.','07-11-2013',1,0)
insert into Ecard values('http://i.123g.us/c/birth_happybirthday/th/122577_th.gif','http://i.123g.us/c/birth_happybirthday/card/122577.swf','Special Birthday Carniva.','07-11-2013',1,0)

--thiep dam cuoi
insert into Ecard values('http://i.123g.us/c/wed_wed_congrats/th/303886_th.jpg','http://i.123g.us/c/wed_wed_congrats/card/303886.swf','God Bless Your Marriage.','07-11-2013',2,0)
insert into Ecard values('http://i.123g.us/c/wed_wed_congrats/th/100008_th.gif','http://i.123g.us/c/wed_wed_congrats/card/100008.swf','Wedding Bells Ringing.','07-11-2013',2,0)
insert into Ecard values('http://i.123g.us/c/wed_wed_congrats/th/122236_th.gif','http://i.123g.us/c/wed_wed_congrats/card/122236.swf','Wedding Wishes For A Couple.','07-11-2013',2,0)
insert into Ecard values('http://i.123g.us/c/wed_wishes/th/115411_th.gif','http://i.123g.us/c/wed_wishes/card/115411.swf','Wedding Wishes For Lovely.','07-11-2013',2,0)
insert into Ecard values('http://i.123g.us/c/invp_wed/th/111460_th.gif','http://i.123g.us/c/invp_wed/card/111460.swf','Wedding Invitation.','07-11-2013',2,0)
insert into Ecard values('http://i.123g.us/c/wed_arndworld/th/110515_th.gif','http://i.123g.us/c/wed_arndworld/card/110515.swf','Christian Wedding.','07-11-2013',2,0)
insert into Ecard values('http://i.123g.us/c/wed_wed_marryme/th/112464_th.gif','http://i.123g.us/c/wed_wed_marryme/card/112464.swf','Anxious Moments.','07-11-2013',2,0)
insert into Ecard values('http://i.123g.us/c/wed_wed_marryme/th/112327_th.gif','http://i.123g.us/c/wed_wed_marryme/card/112327.swf','Will You Be Mine?','07-11-2013',2,0)
insert into Ecard values('http://i.123g.us/c/wed_justmarried/th/100055_th.gif','http://i.123g.us/c/wed_wed_marryme/card/112327.swf','Happy Ever After!','07-11-2013',2,0)
insert into Ecard values('http://i.123g.us/c/wed_announce/th/113623_th.gif','http://i.123g.us/c/wed_announce/card/113623.swf','The Date Card','07-11-2013',2,0)
insert into Ecard values('http://i.123g.us/c/wed_announce/th/112563_th.gif','http://i.123g.us/c/wed_announce/card/112563.swf','Save The Date!.','07-11-2013',2,0)
insert into Ecard values('http://i.123g.us/c/wed_wed_marryme/th/100045_th.gif','http://i.123g.us/c/wed_wed_marryme/card/100045.swf','Make My Wish come true!','07-11-2013',2,0)


--thiep chuc mung nam moi

insert into Ecard values('http://i.123g.us/c/ejan_ny_family/th/107806_th.gif','http://i.123g.us/c/ejan_ny_family/card/107806.swf','Happy New Year!','07-11-2013',3,0)
insert into Ecard values('http://i.123g.us/c/ejan_ny_happy/th/107887_th.gif','http://i.123g.us/c/ejan_ny_happy/card/107887.swf','Sparkling New Year','07-11-2013',3,0)
insert into Ecard values('http://i.123g.us/c/ejan_ny_happy/th/113379_th.gif','http://i.123g.us/c/ejan_ny_happy/card/113379.swf','Big New Year Hug!','07-11-2013',3,0)
insert into Ecard values('http://i.123g.us/c/ejan_ny_happy/th/113846_th.gif','http://i.123g.us/c/ejan_ny_happy/card/113846.swf','Heartfelt New Year Wishes!','07-11-2013',3,0)
insert into Ecard values('http://i.123g.us/c/ejan_ny_happy/th/113859_th.gif','http://i.123g.us/c/ejan_ny_happy/card/113859.swf','Loads Of New Year Hugs.','07-11-2013',3,0)
insert into Ecard values('http://i.123g.us/c/ejan_ny_happy/th/113854_th.gif','http://i.123g.us/c/ejan_ny_happy/card/113854.swf','New Year Teddy Hugs','07-11-2013',3,0)
insert into Ecard values('http://i.123g.us/c/ejan_ny_thanku/th/107829_th.gif','http://i.123g.us/c/ejan_ny_thanku/card/107829.swf','New Year Thanks!','07-11-2013',3,0)
insert into Ecard values('http://i.123g.us/c/ejan_ny_happy/th/109527_th.gif','http://i.123g.us/c/ejan_ny_happy/card/109527.swf','New Year Romantic Wish','07-11-2013',3,0)
insert into Ecard values('http://i.123g.us/c/ejan_ny_happy/th/122024_th.gif','http://i.123g.us/c/ejan_ny_happy/card/122024.swf','New Year Joy And Merriment','07-11-2013',3,0)
insert into Ecard values('http://i.123g.us/c/ejan_ny_happy/th/113850_th.gif','http://i.123g.us/c/ejan_ny_happy/card/113850.swf','Interactive New Year Wish.','07-11-2013',3,0)


--thiep chuc mung Halloween
insert into Ecard values('http://i.123g.us/c/eoct_hallo_thank/th/125091_th.gif','http://i.123g.us/c/eoct_hallo_thank/card/125091.swf','Halloween Thank U Hugs!','07-11-2013',4,0)
insert into Ecard values('http://i.123g.us/c/eoct_hallo_wishes/th/121387_th.gif','http://i.123g.us/c/eoct_hallo_wishes/card/121387.swf','Happy Halloween To U!','07-11-2013',4,0)
insert into Ecard values('http://i.123g.us/c/eoct_hallo_hauntedhouse/th/113189_th.gif','http://i.123g.us/c/eoct_hallo_hauntedhouse/card/113189.swf','Halloween Haunted!','07-11-2013',4,0)
insert into Ecard values('http://i.123g.us/c/eoct_hallo_wishes/th/310441_th.gif','http://i.123g.us/c/eoct_hallo_wishes/card/105128.swf','Magical Halloween!','07-11-2013',4,0)
insert into Ecard values('http://i.123g.us/c/eoct_hallo_wishes/th/105128_th.gif','http://i.123g.us/c/birth_happybirthday/card/114550.swf','Halloween Wishes','07-11-2013',4,0)
insert into Ecard values('http://i.123g.us/c/eoct_hallo_wishes/th/104945_th.gif','http://i.123g.us/c/eoct_hallo_wishes/card/104945.swf','Happy Halloween To You','07-11-2013',4,0)
insert into Ecard values('http://i.123g.us/c/eoct_hallo_wishes/th/113133_th.gif','http://i.123g.us/c/eoct_hallo_wishes/card/113133.swf','Create A Halloween!','07-11-2013',4,0)
insert into Ecard values('http://i.123g.us/c/eoct_hallo_wishes/th/104997_th.gif','http://i.123g.us/c/eoct_hallo_wishes/card/104997.swf','Surprise On Halloween!','07-11-2013',4,0)
insert into Ecard values('http://i.123g.us/c/eoct_hallo_wishes/th/111973_th.gif','http://i.123g.us/c/eoct_hallo_wishes/card/111973.swf','Dark Halloween Night!','07-11-2013',4,0)
insert into Ecard values('http://i.123g.us/c/eoct_hallo_wishes/th/104999_th.gif','http://i.123g.us/c/eoct_hallo_wishes/card/104999.swf','For Your Halloween','07-11-2013',4,0)
insert into Ecard values('http://i.123g.us/c/eoct_hallo_pumpkins/th/105071_th.gif','http://i.123g.us/c/eoct_hallo_pumpkins/card/105071.swf','Have A Terrific Halloween!','07-11-2013',4,0)
insert into Ecard values('http://i.123g.us/c/eoct_hallo_pumpkins/th/105074_th.gif','http://i.123g.us/c/eoct_hallo_pumpkins/card/105074.swf','Halloween Pumpkin!','07-11-2013',4,0)

--Thiep Festilvals
insert into Ecard values('http://i.123g.us/c/ejan_makarsankranti/th/125706_th.gif','http://i.123g.us/c/ejan_makarsankranti/card/125706.swf','Festive Spirit!','07-11-2013',5,0)
insert into Ecard values('http://i.123g.us/c/ejan_lohri/th/122238_th.gif','http://i.123g.us/c/ejan_lohri/card/122238.swf','Lohri Fun And Frolic','07-11-2013',5,0)
insert into Ecard values('http://i.123g.us/c/ejan_lohri/th/122227_th.gif','http://i.123g.us/c/ejan_lohri/card/122227.swf','Joyous Lohri','07-11-2013',5,0)
insert into Ecard values('http://i.123g.us/c/ejan_makarsankranti/th/121242_th.gif','http://i.123g.us/c/ejan_makarsankranti/card/121242.swf','Prosperity','07-11-2013',5,0)
insert into Ecard values('http://i.123g.us/c/edec_hanukkah_happy/th/107502_th.gif','http://i.123g.us/c/edec_hanukkah_happy/card/107502.swf','Festival Of Lights','07-11-2013',5,0)
insert into Ecard values('http://i.123g.us/c/ejan_lohri/th/122240_th.gif','http://i.123g.us/c/ejan_lohri/card/122240.swf','Prosperous Harvest','07-11-2013',5,0)
insert into Ecard values('http://i.123g.us/c/ejan_makarsankranti/th/121241_th.gif','http://i.123g.us/c/ejan_makarsankranti/card/121241.swf','Filled With Sweet','07-11-2013',5,0)
insert into Ecard values('http://i.123g.us/c/esep_harvestmoonfest/th/115794_th.gif','http://i.123g.us/c/esep_harvestmoonfest/card/115794.swf','SMoon Festival Fun','07-11-2013',5,0)
insert into Ecard values('http://i.123g.us/c/esep_harvestmoonfest/th/103717_th.gif','http://i.123g.us/c/esep_harvestmoonfest/card/103717.swf','Wonderful Moon Festival','07-11-2013',5,0)
insert into Ecard values('http://i.123g.us/c/esep_harvestmoonfest/th/119861_th.gif','http://i.123g.us/c/esep_harvestmoonfest/card/119861.swf','With Happiness','07-11-2013',5,0)
insert into Ecard values('http://i.123g.us/c/esep_harvestmoonfest/th/119856_th.gif','http://i.123g.us/c/esep_harvestmoonfest/card/119856.swf','Yummy Surprises','07-11-2013',5,0)
insert into Ecard values('http://i.123g.us/c/ejul_friendfestmissouri/th/123466_th.gif','http://i.123g.us/c/ejul_friendfestmissouri/card/123466.swf','Friendship Festival','07-11-2013',5,0)


--Thiệp Valentine
insert into Ecard values('http://i.123g.us/c/efeb_valen_happy/th/118053_th.gif','http://i.123g.us/c/efeb_valen_happy/card/118053.swf','Valentine Day','07-11-2013',6,0)
insert into Ecard values('http://i.123g.us/c/efeb_valen_happy/th/113826_th.gif','http://i.123g.us/c/efeb_valen_happy/card/113826.swf','Valentine Wishes!','07-11-2013',6,0)
insert into Ecard values('http://i.123g.us/c/efeb_valen_family/th/117771_th.gif','http://i.123g.us/c/efeb_valen_family/card/117771.swf','Valentine Day Hugs!','07-11-2013',6,0)
insert into Ecard values('http://i.123g.us/c/efeb_valen_happy/th/113838_th.gif','http://i.123g.us/c/efeb_valen_happy/card/113838.swf','Valentine Teddy Hugs!','07-11-2013',6,0)
insert into Ecard values('http://i.123g.us/c/efeb_valen_happy/th/113947_th.gif','http://i.123g.us/c/efeb_valen_happy/card/113947.swf','Valentine Smiley','07-11-2013',6,0)
insert into Ecard values('http://i.123g.us/c/efeb_valen_happy/th/122434_th.gif','http://i.123g.us/c/efeb_valen_happy/card/122434.swf','A Special Valentine','07-11-2013',6,0)
insert into Ecard values('http://i.123g.us/c/efeb_valen_happy/th/109551_th.gif','http://i.123g.us/c/efeb_valen_happy/card/109551.swf','A Romantic Valentine','07-11-2013',6,0)
insert into Ecard values('http://i.123g.us/c/efeb_valen_happy/th/107968_th.gif','http://i.123g.us/c/efeb_valen_happy/card/107968.swf','A Valentine Day','07-11-2013',6,0)
insert into Ecard values('http://i.123g.us/c/efeb_valen_happy/th/113564_th.gif','http://i.123g.us/c/efeb_valen_happy/card/113564.swf','Precious Valentine!','07-11-2013',6,0)
insert into Ecard values('http://i.123g.us/c/efeb_valen_happy/th/107967_th.gif','http://i.123g.us/c/efeb_valen_happy/card/107967.swf','Rose On Valentine Day!','07-11-2013',6,0)
insert into Ecard values('http://i.123g.us/c/efeb_valen_happy/th/107401_th.gif','http://i.123g.us/c/efeb_valen_happy/card/107401.swf','Valentine Thoughts!','07-11-2013',6,0)
insert into Ecard values('http://i.123g.us/c/efeb_valen_thanku/th/117785_th.gif','http://i.123g.us/c/efeb_valen_thanku/card/117785.swf','Valentine Day Thank!','07-11-2013',6,0)


--thiep chuc mung ngay nghi
insert into Ecard values('http://i.123g.us/c/edec_happyholidays/th/122119_th.gif','http://i.123g.us/c/edec_happyholidays/card/122119.swf','Holiday Wishes!','07-11-2013',7,0)
insert into Ecard values('http://i.123g.us/c/edec_happyholidays/th/306214_th.jpg','http://i.123g.us/c/edec_happyholidays/card/306214.swf','>Holiday Cheer','07-11-2013',7,0)
insert into Ecard values('http://i.123g.us/c/edec_happyholidays/th/122115_th.gif','http://i.123g.us/c/edec_happyholidays/card/122115.swf','Magical Holiday','07-11-2013',7,0)
insert into Ecard values('http://i.123g.us/c/edec_happyholidays/th/122111_th.gif','http://i.123g.us/c/edec_happyholidays/card/122111.swf','Holiday Hugs!','07-11-2013',7,0)
insert into Ecard values('http://i.123g.us/c/edec_happyholidays/th/122113_th.gif','http://i.123g.us/c/edec_happyholidays/card/122113.swf','Happy Holiday Dance!','07-11-2013',7,0)
insert into Ecard values('http://i.123g.us/c/edec_happyholidays/th/122110_th.gif','http://i.123g.us/c/efeb_valen_happy/card/122434.swf','Celebrate Your Holidays!','07-11-2013',7,0)
insert into Ecard values('http://i.123g.us/c/edec_happyholidays/th/122109_th.gif','http://i.123g.us/c/edec_happyholidays/card/122109.swf','Happy Holidays!','07-11-2013',7,0)
insert into Ecard values('http://i.123g.us/c/edec_happyholidays/th/126174_th.gif','http://i.123g.us/c/edec_happyholidays/card/126174.swf','Happy Holiday Season!','07-11-2013',7,0)
insert into Ecard values('http://i.123g.us/c/edec_happyholidays/th/122112_th.gif','http://i.123g.us/c/edec_happyholidays/card/122112.swf','Chilled Out Holidays!','07-11-2013',7,0)
insert into Ecard values('http://i.123g.us/c/edec_happyholidays/th/306005_th.gif','http://i.123g.us/c/edec_happyholidays/card/306005.swf','Thinking Of You.','07-11-2013',7,0)
insert into Ecard values('http://i.123g.us/c/edec_happyholidays/th/122121_th.gif','http://i.123g.us/c/edec_happyholidays/card/122121.swf','Wish Happy Holidays!','07-11-2013',7,0)
insert into Ecard values('http://i.123g.us/c/edec_happyholidays/th/310592_th.jpg','http://i.123g.us/c/edec_happyholidays/card/310592.swf','Happy Holidays','07-11-2013',7,0)


--thiep giang sinh
insert into Ecard values('http://i.123g.us/c/edec_c_newjingle/th/121426_th.gif','http://i.123g.us/c/edec_c_newjingle/card/121426.swf','Merry Christmas!','07-11-2013',8,0)
insert into Ecard values('http://i.123g.us/c/edec_c_newjingle/th/121261_th.gif','http://i.123g.us/c/edec_c_newjingle/card/121261.swf','Merry Christmas','07-11-2013',8,0)
insert into Ecard values('http://i.123g.us/c/edec_c_newjingle/th/126460_th.gif','http://i.123g.us/c/edec_c_newjingle/card/126460.swf','Merry Christmas Tree!','07-11-2013',8,0)
insert into Ecard values('http://i.123g.us/c/edec_c_newjingle/th/109268_th.gif','http://i.123g.us/c/edec_c_newjingle/card/109268.swf','Merry Christmas','07-11-2013',8,0)
insert into Ecard values('http://i.123g.us/c/edec_c_newjingle/th/117028_th.gif','http://i.123g.us/c/edec_c_newjingle/card/117028.swf','Merry Christmas!','07-11-2013',8,0)
insert into Ecard values('http://i.123g.us/c/edec_c_newjingle/th/109256_th.gif','http://i.123g.us/c/edec_c_newjingle/card/109256.swf','Merry Christmas Cheer!','07-11-2013',8,0)
insert into Ecard values('http://i.123g.us/c/edec_c_newjingle/th/126213_th.gif','http://i.123g.us/c/edec_c_newjingle/card/126213.swf','Beautiful Christmas','07-11-2013',8,0)
insert into Ecard values('http://i.123g.us/c/edec_c_newjingle/th/110583_th.gif','http://i.123g.us/c/edec_c_newjingle/card/110583.swf','Merry Christmas!','07-11-2013',8,0)
insert into Ecard values('http://i.123g.us/c/edec_christworldday_english/th/106261_th.gif','http://i.123g.us/c/edec_happyholidays/card/122112.swf','Merry Christmas','07-11-2013',8,0)
insert into Ecard values('http://i.123g.us/c/edec_c_newjingle/th/117303_th.gif','http://i.123g.us/c/edec_c_newjingle/card/117303.swf','Christmas Hugs And Wishes!','07-11-2013',8,0)
insert into Ecard values('http://i.123g.us/c/edec_c_newjingle/th/117292_th.gif','http://i.123g.us/c/edec_c_newjingle/card/117292.swf','Send Christmas Wishes','07-11-2013',8,0)
insert into Ecard values('http://i.123g.us/c/edec_c_newjingle/th/121944_th.gif','http://i.123g.us/c/edec_c_newjingle/card/121944.swf','Christmas Is Here!','07-11-2013',8,0)


--thiep chuc mung moi ngay
insert into Ecard values('http://i.123g.us/c/gen_morning/th/115940_th.gif','http://i.123g.us/c/gen_morning/card/115940.swf','Coffee In The Morning!','07-11-2013',9,0)
insert into Ecard values('http://i.123g.us/c/gen_morning/th/112250_th.gif','http://i.123g.us/c/gen_morning/card/112250.swf','Wish A Great Morning','07-11-2013',9,0)
insert into Ecard values('http://i.123g.us/c/gen_morning/th/115637_th.gif','http://i.123g.us/c/gen_morning/card/115637.swf','Good Morning!','07-11-2013',9,0)
insert into Ecard values('http://i.123g.us/c/gen_morning/th/119995_th.gif','http://i.123g.us/c/gen_morning/card/119995.swf','A Good Morning Hug!','07-11-2013',9,0)
insert into Ecard values('http://i.123g.us/c/gen_morning/th/116509_th.gif','http://i.123g.us/c/gen_morning/card/116509.swf','Wishing Good Morning','07-11-2013',9,0)
insert into Ecard values('http://i.123g.us/c/gen_morning/th/121609_th.gif','http://i.123g.us/c/gen_morning/card/121609.swf','Welcome A New Morning!','07-11-2013',9,0)
insert into Ecard values('http://i.123g.us/c/gen_morning/th/111919_th.gif','http://i.123g.us/c/gen_morning/card/111919.swf','Cock-a-doodle-doo!','07-11-2013',9,0)
insert into Ecard values('http://i.123g.us/c/gen_morning/th/115410_th.gif','http://i.123g.us/c/gen_morning/card/115410.swf','Good Morning With Smile!','07-11-2013',9,0)
insert into Ecard values('http://i.123g.us/c/gen_morning/th/101712_th.gif','http://i.123g.us/c/gen_morning/card/101712.swf','The Morning News','07-11-2013',9,0)
insert into Ecard values('http://i.123g.us/c/gen_morning/th/112549_th.gif','http://i.123g.us/c/gen_morning/card/112549.swf','Wake Up With A Smile','07-11-2013',9,0)
insert into Ecard values('http://i.123g.us/c/gen_morning/th/101716_th.gif','http://i.123g.us/c/gen_morning/card/101716.swf','Morning Message','07-11-2013',9,0)
insert into Ecard values('http://i.123g.us/c/gen_morning/th/112932_th.gif','http://i.123g.us/c/gen_morning/card/112932.swf','Morning With A Smile!','07-11-2013',9,0)


--thiep ban be
insert into Ecard values('http://i.123g.us/c/friend_specialfriend/th/124383_th.gif','http://i.123g.us/c/friend_specialfriend/card/124383.swf','My Special Friend!','07-11-2013',10,0)
insert into Ecard values('http://i.123g.us/c/friend_bestfriends/th/111977_th.gif','http://i.123g.us/c/friend_bestfriends/card/111977.swf','Hey, You!','07-11-2013',10,0)
insert into Ecard values('http://i.123g.us/c/friend_bestfriends/th/302791_th.jpg','http://i.123g.us/c/friend_bestfriends/card/302791.swf','I Pray For You!','07-11-2013',10,0)
insert into Ecard values('http://i.123g.us/c/friend_hello/th/102534_th.gif','http://i.123g.us/c/friend_hello/card/102534.swf','Hello Friend','07-11-2013',10,0)
insert into Ecard values('http://i.123g.us/c/thank_friendship/th/112516_th.gif','http://i.123g.us/c/thank_friendship/card/112516.swf','A Friend Like You','07-11-2013',10,0)
insert into Ecard values('http://i.123g.us/c/thank_friendship/th/113414_th.gif','http://i.123g.us/c/thank_friendship/card/113414.swf','Came By To Say','07-11-2013',10,0)
insert into Ecard values('http://i.123g.us/c/friend_funny/th/101904_th.gif','http://i.123g.us/c/friend_funny/card/101904.swf','Special Friendship Rose!!','07-11-2013',10,0)
insert into Ecard values('http://i.123g.us/c/friend_hugsncaring/th/101877_th.gif','http://i.123g.us/c/friend_hugsncaring/card/101877.swf','Friend Who Wonderful!','07-11-2013',10,0)
insert into Ecard values('http://i.123g.us/c/friend_hugsncaring/th/101879_th.gif','http://i.123g.us/c/friend_hugsncaring/card/101879.swf','A Million Hugs','07-11-2013',10,0)
insert into Ecard values('http://i.123g.us/c/friend_hugsncaring/th/101866_th.gif','http://i.123g.us/c/friend_hugsncaring/card/101866.swf','You Have Been Hugged','07-11-2013',10,0)
insert into Ecard values('http://i.123g.us/c/friend_teenfriends/th/102299_th.gif','http://i.123g.us/c/friend_teenfriends/card/102299.swf','Friend-o-meter','07-11-2013',10,0)
insert into Ecard values('http://i.123g.us/c/friend_justpals/th/102500_th.gif','http://i.123g.us/c/friend_justpals/card/102500.swf','Wonderful Friendship!','07-11-2013',10,0)


--thiep cam on
insert into Ecard values('http://i.123g.us/c/thank_friendship/th/113414_th.gif','http://i.123g.us/c/thank_friendship/card/113414.swf','Came By To Say','07-11-2013',11,0)
insert into Ecard values('http://i.123g.us/c/thank_friendship/th/101081_th.gif','http://i.123g.us/c/thank_friendship/card/101081.swf','A Big Thank You','07-11-2013',11,0)
insert into Ecard values('http://i.123g.us/c/thank_friendship/th/123387_th.gif','http://i.123g.us/c/thank_friendship/card/123387.swf','Thanks For Everything','07-11-2013',11,0)
insert into Ecard values('http://i.123g.us/c/thank_family/th/111425_th.gif','http://i.123g.us/c/thank_family/card/111425.swf','A Special Thanks!','07-11-2013',11,0)
insert into Ecard values('http://i.123g.us/c/thank_friendship/th/112516_th.gif','http://i.123g.us/c/thank_friendship/card/112516.swf','Thank you','07-11-2013',11,0)
insert into Ecard values('http://i.123g.us/c/thank_friendship/th/113414_th.gif','http://i.123g.us/c/thank_friendship/card/113414.swf','thanks Dear Fiend','07-11-2013',11,0)
insert into Ecard values('http://i.123g.us/c/thank_friendship/th/306475_th.jpg','http://i.123g.us/c/thank_friendship/card/306475.swf','Breathing A Rose!!','07-11-2013',11,0)
insert into Ecard values('http://i.123g.us/c/thank_friendship/th/119460_th.gif','http://i.123g.us/c/thank_friendship/card/119460.swf','The Little Things You!','07-11-2013',11,0)
insert into Ecard values('http://i.123g.us/c/thank_friendship/th/121796_th.gif','http://i.123g.us/c/thank_friendship/card/121796.swf','For A Great Friend!','07-11-2013',11,0)
insert into Ecard values('http://i.123g.us/c/thank_friendship/th/101092_th.gif','http://i.123g.us/c/thank_friendship/card/101092.swf','For Your Buddy!','07-11-2013',11,0)
insert into Ecard values('http://i.123g.us/c/thank_friendship/th/121956_th.gif','http://i.123g.us/c/thank_friendship/card/121956.swf','Good Friend To Me','07-11-2013',11,0)
insert into Ecard values('http://i.123g.us/c/thank_friendship/th/116345_th.gif','http://i.123g.us/c/thank_friendship/card/116345.swf','For Thanking A Friend!','07-11-2013',11,0)


--thiep gia dinh
insert into Ecard values('http://i.123g.us/c/fkt_loveyoumom/th/111340_th.gif','http://i.123g.us/c/fkt_loveyoumom/card/111340.swf','Love You Mom!','07-11-2013',12,0)
insert into Ecard values('http://i.123g.us/c/fkt_loveyoumom/th/101728_th.gif','http://i.123g.us/c/fkt_loveyoumom/card/101728.swf','I Love You Mom','07-11-2013',12,0)
insert into Ecard values('http://i.123g.us/c/fkt_loveyoumom/th/101691_th.gif','http://i.123g.us/c/fkt_loveyoumom/card/101691.swf','A Mom Like You','07-11-2013',12,0)
insert into Ecard values('http://i.123g.us/c/fkt_loveyoudad/th/108012_th.gif','http://i.123g.us/c/fkt_loveyoudad/card/108012.swf','Being Daddy Girl!!','07-11-2013',12,0)
insert into Ecard values('http://i.123g.us/c/fkt_loveyoudad/th/111358_th.gif','http://i.123g.us/c/fkt_loveyoudad/card/111358.swf','Best Dad!','07-11-2013',12,0)
insert into Ecard values('http://i.123g.us/c/fkt_missyou/th/101790_th.gif','http://i.123g.us/c/fkt_missyou/card/101790.swf','Always On My Mind!','07-11-2013',12,0)
insert into Ecard values('http://i.123g.us/c/fkt_babies/th/101269_th.gif','http://i.123g.us/c/fkt_babies/card/101269.swf','Your Baby Girl!!','07-11-2013',12,0)
insert into Ecard values('http://i.123g.us/c/fkt_familyoccasions/th/111325_th.gif','http://i.123g.us/c/fkt_familyoccasions/card/111325.swf','For The Baby!','07-11-2013',12,0)
insert into Ecard values('http://i.123g.us/c/fkt_loveyoudad/th/124505_th.gif','http://i.123g.us/c/fkt_loveyoudad/card/124505.swf','The World To Me','07-11-2013',12,0)
insert into Ecard values('http://i.123g.us/c/fkt_loveyoudad/th/114359_th.gif','http://i.123g.us/c/fkt_loveyoudad/card/114359.swf','To-do List For Dad!!','07-11-2013',12,0)
insert into Ecard values('http://i.123g.us/c/fkt_loveyoudad/th/121249_th.gif','http://i.123g.us/c/fkt_loveyoudad/card/121249.swf','Dad, My Best Friend!','07-11-2013',12,0)
insert into Ecard values('http://i.123g.us/c/fkt_loveyoudad/th/115032_th.gif','http://i.123g.us/c/fkt_loveyoudad/card/115032.swf','Day Dad!','07-11-2013',12,0)




go
create table sentEcard
(
	sentEcardId int primary key identity,
	customerId int foreign key references customer(customerId),
	ecardId int foreign key references Ecard(ecardId),
	dateSent datetime not null,
	title varchar(100),
	messages varchar(1000),
	signatures varchar(25),
)
go



--select * from sentecard

insert into sentEcard values(2,1,'11-21-2013','happy birthday','happy happy','hero')
insert into sentEcard values(1,2,'11-11-2013','happy birthday','happy happy','hero')


-- sap xep nguoc lay id
--select top 1 sentEcardId from sentEcard where customerId = 1 order by sentEcardId desc 

go
go 
create table listEmail
(
	sentEcardId int foreign key references sentEcard(sentEcardId),
	emailReceived varchar(100),
	nameReceived varchar(25)
)

--select * from listEmail

insert into listEmail values(1,'nguoimua12@gmail.com','linh')
insert into listEmail values(2,'nguoiban1234567@gmail.com','hien')
go
create table feedback
(
	feedbackId int primary key identity ,
	customerId int foreign key references customer(customerId),
	content varchar(1000),
	dateSent datetime
)
--select * from feedback
go
create table admins
(
	id int primary key identity,
	username varchar(25) not null,
	passwords varchar(25) not null
)
go
insert into admins values('admin','123456')
go
create table logSentMail
(
	LogSentMailId int primary key identity,
	customerId int foreign key references customer(customerId),
	nameSender varchar(25),
	emailSender varchar(100),
	ecardId int foreign key references Ecard(ecardId),
	dateSent datetime,
	title varchar(30),
	messages varchar(300),
	--photo varchar(200),
	--video varchar(200),
	signatures varchar(25),
	emailReceived varchar(100),
	nameReceived varchar(25)
)
--select * from logSentMail group by customerId 
--select customerId,COUNT(customerId) as numberSent from logSentMail group by customerId 
go

--select * from thongKeGiaoDichHoanThanhTheoCustomer

-- tao cac view
go
create view Statistics_count
as
	 select typeCardName , SUM(Ecard.counts) as sent 
	 from Ecard inner join typeEcard on typeEcard.typeEcardId = Ecard.typeEcardId
	 --where  DATEDIFF(dd,dateSent,GETDATE()) = 0  
	 Group by  typeEcard.typeCardName
go
-- hien thi Ecard
create view displayEcard
as
select ecardId , urlImage , urlFlash , ecardName , addDate , E.typeEcardId , counts , TE.typeCardName 
		from Ecard E inner join typeEcard TE on E.typeEcardId = TE.typeEcardId 
go
--select sum(sent) as total from Statistics_count

-- tinh so lan gui card trong ngay hom nay
--select count(*) from sentEcard where DATEDIFF(dd,dateSent,GETDATE()) = 0
--insert into logSentMail values(1,'thanh','vanthanh26041989@gmail.com',1,GETDATE(),'happy birthday','happy happy ','satthu','nguoimua12@gmail.com','linh')
-- thong ke transaction done
create view thongKeGiaoDichHoanThanhTheoCustomer
as
	select customer.customerId, customer.name ,customer.email, COUNT(logSentMail.LogSentMailId) as numberOfSent 
	from customer inner join logSentMail on customer.customerId = logSentMail.customerId 
	group by customer.customerId,customer.name,customer.email
go
go
create view thongKeGiaoDichHoanThanhTheoCustomerByDay
as
	select customer.customerId, customer.name ,customer.email, COUNT(logSentMail.LogSentMailId) as numberOfSent 
	from customer inner join logSentMail on customer.customerId = logSentMail.customerId 
	where DATEDIFF(dd,dateSent,GETDATE()) = 0 
	group by customer.customerId,customer.name,customer.email
go
go
-- thong ke cac giao dich trong ngay
create view StatisticTransactionByToday
as
	select sentEcard.customerId,customer.name,customer.email, nameReceived,emailReceived,sentEcard.ecardId, sentEcard.dateSent 
	from sentEcard inner join listEmail on sentEcard.sentEcardId = listEmail.sentEcardId inner join customer on customer.customerId = sentEcard.customerId 
	where DATEDIFF(dd,dateSent,GETDATE()) = 0 
	group by sentEcard.customerId,customer.name,customer.email, nameReceived,emailReceived,sentEcard.ecardId,sentEcard.dateSent
go
-- so lan gui mail trong ngay
go
create view numberOfSentMailInToday
as
select count(*) as sent from sentEcard where  DATEDIFF(dd,dateSent,GETDATE()) = 0 
go
--view thong ke Ecard dc gui trong thang
go
create view EcardSentInMonth
as
	 select Ecard.ecardId, typeCardName,Count(Ecard.typeEcardId) as sent  
	 from sentEcard inner join Ecard on sentEcard.ecardId = Ecard.ecardId inner join typeEcard on Ecard.typeEcardId = typeEcard.typeEcardId 
	 where DATEDIFF(mm,dateSent,GETDATE()) = 0  
	 group by Ecard.ecardId  ,typeCardName 
	
go

--select  top 5 *   from EcardSentInMonth order by sent desc 
--view typeEcard gui nhieu trog thang
go
create view TypeEcardSentInMonth
as
	 select  typeCardName,Count(Ecard.typeEcardId) as sent  
	 from sentEcard inner join Ecard on sentEcard.ecardId = Ecard.ecardId inner join typeEcard on Ecard.typeEcardId = typeEcard.typeEcardId 
	 where DATEDIFF(MM,dateSent,GETDATE()) = 0  
	 group by typeCardName 
	
go
-- tao view chi tiet subcrible
go
create view SubcribleDetail
as
select customer.customerId,email,datefrom,dateto, subcrileName ,datediff(dd,  GetDate(), dateto) as expiration
	from customer inner join subcrible 
	on customer.customerId = subcrible.customerId inner join subcribleType 
	on subcrible.subcribleTypeId = subcribleType.subcribleTypeId
go
go
create view view_feedback
as
select feedback.feedbackId, customer.customerId ,customer.name,customer.email,feedback.content from feedback  inner join customer  on customer.customerId = feedback.customerId
go
